/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package cn.ac.iie.di.wx.statweb.utils;

import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Statement;

/**
 *
 * @author Administrator
 */
public class DBHelper {

    public static final String url = "jdbc:mysql://localhost:3306/plotdata?useUnicode=true&characterEncoding=UTF-8";
    public static final String name = "com.mysql.jdbc.Driver";
    public static final String user = "plot";
    public static final String password = "plot";

    public Connection conn = null;
    public Statement pst = null;

    public DBHelper(String sql) {
        try {
            Class.forName(name);//指定连接类型  
            conn = DriverManager.getConnection(url, user, password);//获取连接  
            if (!conn.isClosed()) {
                System.out.println("Succeeded connecting to the Database!");
            }
            pst = conn.createStatement();//准备执行语句  
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void close() {
        try {
            this.conn.close();
            this.pst.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        String sql = "select * from alerm where status = 0;"; //SQL语句  
        DBHelper db1 = new DBHelper(sql); //创建DBHelper对象  

        try {
            ResultSet ret = db1.pst.executeQuery(sql); //执行语句，得到结果集  
            while (ret.next()) {
                String uid = ret.getString(1);
                String ufname = ret.getString(2);
                String ulname = ret.getString(3);
                String udate = ret.getString(4);
                System.out.println(uid + "\t" + ufname + "\t" + ulname + "\t" + udate);
            }//显示数据  
            updateById("11");
            ret.close();
            db1.close();//关闭连接  
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    public static boolean updateById(String id) throws SQLException {
        boolean flag = false;
        String sql = "update alerm set status = 1 where id = " + id + ";"; //SQL语句  
        DBHelper db1 = new DBHelper(sql); //创建DBHelper对象  
        int result = db1.pst.executeUpdate(sql);
        System.out.println(result);
        if (result == 1) {
            System.out.println("success");
            flag = true;
        }
        return flag;
    }
        public static boolean updateRemarkById(String id,String remark) throws SQLException, UnsupportedEncodingException {
        boolean flag = false;
        String sql = "update alerm set remark = '"+remark+"' where id = " + id + ";"; //SQL语句  
        sql = new String(sql.getBytes(), "UTF-8");
        DBHelper db1 = new DBHelper(sql); //创建DBHelper对象  
        int result = db1.pst.executeUpdate(sql);
        System.out.println(result);
        if (result == 1) {
            System.out.println("success");
            flag = true;
        }
        return flag;
    }
}
